package com.hung.pro.iFlashCards.dao;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * Common DB management class
 * User: hung
 * Date: 2008/05/01
 * Time: 16:15:06
 * To change this template use File | Settings | File Templates.
 */
public class DBDao {

    /**
     * default DB name
     */
    private static final String DEFAULT_DB_NAME = "iFlashCards";

    /**
     * Create CARD table sql
     */
    private static final String CREATE_CARD_TABLE = "" +
            "CREATE TABLE CARD (" +
            "ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS  AS IDENTITY                    " +
            "( START WITH 1, INCREMENT BY 1),            " +
            "LEXICON INTEGER  REFERENCES LEXICON(ID) ON DELETE CASCADE,    " +
            "WORD VARCHAR(255) NOT NULL,                " +
            "READING VARCHAR(255) ,             " +
            "MEANING VARCHAR(255) ,             " +
            "EXAMPLE VARCHAR(255) ,             " +
            "NOTE VARCHAR(255),                          " +
            "STATUS INT NOT NULL" +
            ")";

    /**
     * Create LEXICON table
     */
    private static final String CREATE_LEXICON_TABLE = "" +
            "CREATE TABLE LEXICON (" +
            "ID INTEGER NOT NULL PRIMARY KEY  GENERATED ALWAYS  AS IDENTITY ( START WITH 1, INCREMENT BY 1),            " +
            "NAME VARCHAR(255) NOT NULL UNIQUE  " +
            ")";


    /**
     * DB connection
     */
    private Connection connection;

    /**
     * DB name
     */
    private String dbName;

    /**
     * DB properties
     */
    private Properties dbProperties;

    /**
     * Connected or not
     */
    private boolean isConnected;


    /**
     * New instance with dbName input
     *
     * @param dbName database name
     */
    public DBDao(String dbName) {
        this.dbName = dbName;
        setDBSystemDir();
        dbProperties = loadDBProperties();
        String driverName = dbProperties.getProperty("derby.driver");
        loadDatabaseDriver(driverName);
        if (!dbExists()) {
            createDatabase();
        } else {
            connect();
        }
    }

    public DBDao() {
        this(DEFAULT_DB_NAME);
    }

    /**
     * Read DB config from file
     *
     * @return dbProperties DB connection information
     */
    private Properties loadDBProperties() {
        InputStream dbPropInputStream = null;
        dbPropInputStream = DBDao.class.getResourceAsStream("DBConfig.properties");
        dbProperties = new Properties();
        try {
            dbProperties.load(dbPropInputStream);
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return dbProperties;
    }

    /**
     * Load database driver
     *
     * @param driverName database driver name
     */
    private void loadDatabaseDriver(String driverName) {
        // load Derby driver
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }

    }

    private boolean dbExists() {
        boolean bExists = false;
        String dbLocation = getDatabaseLocation();
        File dbFileDir = new File(dbLocation);
        if (dbFileDir.exists()) {
            bExists = true;
        }
        return bExists;
    }

    private void setDBSystemDir() {
        // decide on the db system directory
        String userHomeDir = System.getProperty("user.home", ".");
        String systemDir = userHomeDir + "/.iFlashCards";
        System.setProperty("derby.system.home", systemDir);

        // create the db system directory
        File fileSystemDir = new File(systemDir);
        fileSystemDir.mkdir();
    }

    public String getDatabaseLocation() {
        String dbLocation = System.getProperty("derby.system.home") + "/" + dbName;
        return dbLocation;
    }

    public String getDatabaseUrl() {
        String dbUrl = dbProperties.getProperty("derby.url") + dbName;
        return dbUrl;
    }

    public boolean connect() {
        if (isConnected && connection != null) {
            return isConnected;
        }
        String dbUrl = getDatabaseUrl();
        try {
            connection = DriverManager.getConnection(dbUrl, dbProperties);


            isConnected = connection != null;
        } catch (SQLException ex) {
            isConnected = false;
        }
        return isConnected;
    }

    public void disconnect() {
        if (isConnected) {
            String dbUrl = getDatabaseUrl();
            dbProperties.put("shutdown", "true");
            try {
                DriverManager.getConnection(dbUrl, dbProperties);
            } catch (SQLException ex) {
            }
            isConnected = false;
        }
    }

    private boolean createDatabase() {
        boolean bCreated = false;

        String dbUrl = getDatabaseUrl();
        dbProperties.put("create", "true");

        try {
            connection = DriverManager.getConnection(dbUrl, dbProperties);
            createTables(connection);
            isConnected = true;

        } catch (SQLException ex) {
        }
        dbProperties.remove("create");
        return bCreated;
    }

    private boolean createTables(Connection dbConnection) {
        boolean bCreatedTables = false;
        Statement statement = null;
        try {
            statement = dbConnection.createStatement();
            //Create lexicon table
            statement.execute(CREATE_LEXICON_TABLE);
            statement.close();
            //create card table
            statement = dbConnection.createStatement();
            statement.execute(CREATE_CARD_TABLE);
            statement.close();
            bCreatedTables = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return bCreatedTables;
    }

    public Connection getConnection() {
        return connection;
    }

    public static void main(String[] args) {
        DBDao db = new DBDao();
        System.out.println(db.getDatabaseLocation());
        System.out.println(db.getDatabaseUrl());
        db.connect();
        db.disconnect();
    }
}
